package com.gome.TestUtil;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.io.PrintStream;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.zip.ZipEntry;
import java.util.zip.ZipException;
import java.util.zip.ZipFile;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.ParserConfigurationException;
import org.w3c.dom.Element;
import org.w3c.dom.Document;
import org.w3c.dom.NodeList;
import org.xml.sax.SAXException;

public class ReadExcel {

	/**
     * @param args
     */
    public static void main(String[] args) {
    	
    	SimpleDateFormat format = new SimpleDateFormat("yyyyMMddhhmmss");
		String now = format.format(new Date());
		String fileUrl = "e://log//";
		String fileName = fileUrl + now + ".txt";
		File file = new File(fileUrl);
		PrintStream out = null;
		if (file.exists()) {
			try {
				out = new PrintStream(fileName);
			} catch (FileNotFoundException e) {
				e.printStackTrace();
			}
		} else {
			file.mkdirs();
			try {
				out = new PrintStream(fileName);
			} catch (FileNotFoundException e) {
				e.printStackTrace();
			}
		}
	//	System.setOut(out);
    	
    	
        // 解压Book1.xlsx
        ZipFile xlsxFile;
        try {
            xlsxFile = new ZipFile(new File("e:\\hemaoji.xlsx"));
            DocumentBuilderFactory dbf = DocumentBuilderFactory.newInstance();
 
            // 先读取sharedStrings.xml这个文件备用
            ZipEntry sharedStringXML = xlsxFile.getEntry("xl/sharedStrings.xml");
            InputStream sharedStringXMLIS = xlsxFile
                    .getInputStream(sharedStringXML);
            Document sharedString;
            sharedString = dbf.newDocumentBuilder().parse(sharedStringXMLIS);
            NodeList str = sharedString.getElementsByTagName("t");
            String sharedStrings[] = new String[str.getLength()];
            for (int n = 0; n < str.getLength(); n++) {
                Element element = (Element) str.item(n);
                sharedStrings[n] = element.getTextContent();
            }
            // 找到解压文件夹里的workbook.xml,此文件中包含了这张工作表中有几个sheet
            ZipEntry workbookXML = xlsxFile.getEntry("xl/workbook.xml");
            InputStream workbookXMLIS = xlsxFile.getInputStream(workbookXML);
            Document doc = dbf.newDocumentBuilder().parse(workbookXMLIS);
            // 获取一共有几个sheet
            NodeList nl = doc.getElementsByTagName("sheet");
 
            for (int i = 0; i < nl.getLength(); i++) {
                Element element = (Element) nl.item(i);// 将node转化为element，用来得到每个节点的属性
                System.out.println(element.getAttribute("name"));// 输出sheet节点的name属性的值
                ZipEntry sheetXML = xlsxFile.getEntry("xl/worksheets/"
                        + element.getAttribute("name").toLowerCase() + ".xml");
                InputStream sheetXMLIS = xlsxFile.getInputStream(sheetXML);
                Document sheetdoc = dbf.newDocumentBuilder().parse(sheetXMLIS);
                NodeList rowdata = sheetdoc.getElementsByTagName("row");
                for (int j = 0; j < rowdata.getLength(); j++) {
                    Element row = (Element) rowdata.item(j);
                    // 根据行得到每个行中的列
                    NodeList columndata = row.getElementsByTagName("c");
                    for (int k = 0; k < columndata.getLength(); k++) {
                        Element column = (Element) columndata.item(k);
                        NodeList values = column.getElementsByTagName("v");
                        Element value = (Element) values.item(0);
                        
                 
                        if (column.getAttribute("t") != null
                                & column.getAttribute("t").equals("s")) {
                            // 如果是共享字符串则在sharedstring.xml里查找该列的值
                        
                          //System.out.print(sharedStrings[Integer.parseInt(value.getTextContent())]+ " ");
                        	
                            
                        } else {
                            if (value != null) {
                                System.out.print(value.getTextContent() + " ");
                            }else {
                                System.out.println("j : " + j + "   k : " + k + "  null");
                            }
                        }
                    }
                    System.out.println();
                }
            }
        } catch (ZipException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } catch (SAXException e) {
            e.printStackTrace();
        } catch (ParserConfigurationException e) {
            e.printStackTrace();
        }
         
    }
}
